﻿using System;
using System.Windows;
using System.Xml;
using System.IO;
using System.Linq;
using System.Collections.Generic;
using System.Collections;
using InteractChartCommonLib;

namespace XLSXparsingLib
{
    public class ExcelClass : IFileDropFormat
    {
        private List<Stream> streams = new List<Stream>();
        private Stream getStyles = null;
        

        private Stream Wookbooknames = null;
        private List<string> stringTable = new List<string>();
        private IEnumerable<List<string>> headers = new List<List<string>>();
        private IEnumerable<List<string>> returnedlist = new List<List<string>>();
        private List<string> listSheetNames = new List<string>();
        private FileInfo theFile;

        // to delete
        public List<CInteractChartSerie> GetSerie(int WorksheetIdex)
        {
            return null;
        }

        public ExcelClass(FileInfo _excelFile)
        {
            theFile = _excelFile;
            Stream s = theFile.OpenRead();
            UnZipper unzip;
            unzip = new UnZipper(s);

            Stream sharedStringsStream = s;
            Stream WorkSheetStream = s;

            foreach (string filename in unzip.GetFileNamesInZip())
            {
                Stream stream = unzip.GetFileStream(filename);
                String filenameCompare = filename;
                StreamReader reader = new StreamReader(stream);
                String sharedStringsCompmare;
                String findStyles;

                findStyles = @"xl/styles.xml";
                String findSheetsName = @"xl/workbook.xml";
                sharedStringsCompmare = @"xl/sharedStrings.xml";
                String filenamefind = @"xl/worksheets/";

                if (filenameCompare.Contains(filenamefind) && filename.Contains("rels") == false)
                {
                    streams.Add(stream);
                }

                if (filenameCompare == sharedStringsCompmare)
                {
                    sharedStringsStream = stream;
                }

                if (filenameCompare == findStyles)
                {
                    getStyles = stream;
                }
                if (filenameCompare == findSheetsName)
                {
                    Wookbooknames = stream;

                }
            }

            stringTable = ReadStringTable(sharedStringsStream);
            listSheetNames = getWorkbookNames(Wookbooknames, streams.Count());

            sharedStringsStream.Close();


        }

        public List<string> GetListSubItems() {  return listSheetNames; } 

        public string GetFilename() { return theFile.Name; } 
        
        public List<string> ListSheetNames { get{return listSheetNames; } }
        public IEnumerable<List<string>> Returnedlist { get { return returnedlist; } }

        


        public List<string> ReadStringTable(Stream input)
        {

            List<string> stringTable = new List<string>();


            using (XmlReader reader = XmlReader.Create(input))
            {
                for (reader.MoveToContent(); reader.Read(); )
                    if (reader.NodeType == XmlNodeType.Element && reader.Name == "t")
                        stringTable.Add(reader.ReadElementContentAsString());

            }

            return stringTable;
        }

        public List<string> getWorkbookNames(Stream workbook, int count)
        {

            List<string> workBookTable = new List<string>();
            int Count = count;

            using (XmlReader reader = XmlReader.Create(workbook))
            {
                for (reader.MoveToContent(); reader.Read(); )
                    if (reader.NodeType == XmlNodeType.Element && reader.Name == "sheets")
                    {
                        reader.Read();

                        if (reader.Name == "sheet")
                        {
                            for (int i = 0; i < Count; i++)
                            {
                                workBookTable.Add(reader.GetAttribute("name"));
                                reader.Read();
                            }
                        }
                    }
            }

            return workBookTable;
        }

        public object[] ReadWorksheet(Stream input, List<string> stringTable, Stream style, int rowID)
        {

            List<List<string>> workbook = new List<List<string>>();
            List<List<string>> headers = new List<List<string>>();
            List<string> cHeader = new List<string>();
            List<string> cValue = new List<string>();
            List<string> row = new List<string>();

            Stream StyleStream = style;
            String type;
            String STRFinder;
            Int32 StyleIndex = 0;
            Int32 StyleInt = 0;
            String nullFinder;
            Int32 val = -1;
            String firstLook = null;
            int headerCount = 0;
            bool runRow = false;
            List<string> styleValues = new List<string>();
            int getRowID = rowID;

            string newstr = string.Empty;
            object[] objects = new object[2];

            if (getRowID == 0)
            {
                getRowID = 1;
            }
            
            XmlReader Stylereader = Stylereader = XmlReader.Create(StyleStream);
            Stylereader.MoveToContent();

            while (Stylereader.Read())
            {
                if (Stylereader.NodeType == XmlNodeType.Element)
                {
                    switch (Stylereader.Name)
                    {
                        case "cellXfs":
                            Int16 Count = Convert.ToInt16(Stylereader.GetAttribute("count"));
                            Stylereader.Read();
                            for (int i = 0; i < Count; i++)
                            {
                                if (Stylereader.Name == "xf")
                                {
                                    styleValues.Add(Stylereader.GetAttribute("numFmtId"));
                                    Stylereader.Skip();
                                }
                            }
                            break;
                    }
                }
            }


            using (XmlReader reader = XmlReader.Create(input))
            {
                int currentPostion = 0;
                int postionFound = 0;

                for (reader.MoveToContent(); reader.Read(); )

                    if (reader.NodeType == XmlNodeType.Element)
                    {
                        switch (reader.Name)
                        {
                            case "row":
                                int getRowVale = Convert.ToInt32(reader.GetAttribute("r"));
                                if (getRowVale >= getRowID)
                                {
                                    runRow = true;
                                }
                                else
                                {
                                    runRow = false;
                                }
                                break;

                            case "c":
                                if (runRow == true)
                                {
                                    type = reader.GetAttribute("t");
                                    nullFinder = reader.GetAttribute("r");
                                    if (nullFinder == "K3")
                                    {

                                    }
                                    StyleIndex = Convert.ToInt16(reader.GetAttribute("s"));
                                    STRFinder = reader.GetAttribute("t");

                                    if (StyleIndex > 0)
                                    {
                                        StyleInt = Convert.ToInt32(styleValues[StyleIndex]);
                                    }
                                    newstr = string.Empty;
                                    postionFound = 0;

                                    if (nullFinder != null)
                                    {
                                        foreach (char c in nullFinder)
                                        {
                                            try
                                            {
                                                int i = (int)c;
                                                if ((i >= 48) && (i <= 57))
                                                {
                                                    continue;
                                                }
                                            }
                                            catch (Exception)
                                            {

                                            }
                                            newstr += c.ToString();
                                        }
                                        foreach (char c in newstr)
                                        {
                                            postionFound += ((int)c);
                                        }

                                        if (newstr.Length == 1)
                                        {
                                            postionFound = postionFound - 65;
                                        }
                                        else if (newstr.Length == 2)
                                        {
                                            postionFound = postionFound - 104;
                                        }
                                        else if (newstr.Length == 3)
                                        {
                                            postionFound = postionFound - 119;
                                        }

                                        if (currentPostion != postionFound)
                                        {
                                            for (int i = currentPostion; i < postionFound; i++)
                                            {
                                                row.Add(Convert.ToString(null));
                                            }
                                            currentPostion = postionFound + 1;
                                        }
                                        else
                                        {
                                            currentPostion = postionFound + 1;
                                        }
                                    }

                                    try
                                    {
                                        //skips the formula element to the value
                                        //only allows "c" with elements skips empty elemets
                                        if ((bool)reader.IsEmptyElement == false)
                                        {
                                            reader.Read();

                                            if (reader.Name.Equals("f"))
                                            {
                                                reader.Skip();
                                                firstLook = reader.ReadElementContentAsString().ToString();
                                                row.Add(firstLook);
                                                break;
                                            }
                                            else
                                            {
                                                firstLook = reader.ReadElementContentAsString().ToString();
                                                if (firstLook.Contains("."))
                                                {
                                                    row.Add(firstLook);
                                                    break;
                                                }
                                                else
                                                {
                                                    val = int.Parse(firstLook);
                                                }
                                            }
                                        }
                                        else
                                        {
                                            break;
                                        }
                                    }
                                    catch (FormatException)
                                    {
                                        MessageBox.Show("invaild data type " + reader.ReadElementContentAsString());
                                    }
                                }
                                else
                                {
                                    break;
                                }

                                if (type == "s")
                                {
                                    if (headerCount == 0)
                                    {
                                        cHeader.Add(newstr);
                                        cValue.Add(stringTable[val]);

                                        newstr = string.Empty;
                                    }
                                    else
                                    {
                                        row.Add(stringTable[val]);
                                    }
                                    break;
                                }

                                else if (StyleInt > 13 && StyleInt < 23)
                                {
                                    DateTime dt = DateTime.FromOADate(val);
                                    String DateandTime = Convert.ToString(dt.ToShortDateString());

                                    row.Add(DateandTime);
                                    StyleInt = 0;
                                    StyleIndex = 0;
                                }
                                //else if (STRFinder == "str")
                                //{
                                //    //string readSTR = reader.ReadElementContentAsString().ToString();
                                //    row.Add(Convert.ToString(readSTR));
                                //    break;
                                //}
                                else
                                {
                                    row.Add(Convert.ToString(val));
                                }
                                break;
                        }

                    }
                    else if (reader.NodeType == XmlNodeType.EndElement)
                        if (reader.Name == "row")
                        {
                            if (headerCount == 0 && cValue.Count != 0)
                            {
                                headers.Add(cHeader);
                                headers.Add(cValue);
                                headerCount += 1;
                                currentPostion = 0;
                                postionFound = 0;
                            }
                            else if (row.Count != 0)
                            {
                                if (row.Count != cHeader.Count)
                                {
                                    for (int i = row.Count; i < cHeader.Count; i++)
                                    {
                                        row.Add(Convert.ToString(null));
                                    }
                                }

                                List<string> newRow = new List<string>();

                                foreach (string item in row)
                                {
                                    newRow.Add(item);
                                }
                                workbook.Add(newRow);

                                currentPostion = 0;
                                postionFound = 0;
                                row.Clear();
                            }
                        }

                objects[0] = workbook;
                objects[1] = headers;
                return objects;
            }
        }

        public IEnumerable<IDictionary> GenerateData(IEnumerable<List<string>> data, IEnumerable<List<string>> Headers)
        {
            var key = headers.First();
            var values = headers.Last();

            foreach (List<string> row in data)
            {
                var dict = new Dictionary<string, object>();

                for (int j = 0; j < key.Count(); j++)
                {
                    dict[key[j]] = row[j];
                }
                yield return dict;
            }
        }

        public Dictionary<string,string> GetData(string itemSelected)
        {
       
            returnedlist = null;
            
            object[] returnlists = new object[2];
            int j = listSheetNames.Count() - 1;

            for (int i = 0; i < listSheetNames.Count(); i++)
            {
                if (listSheetNames[i] == itemSelected)
                {
                    returnlists = ReadWorksheet(streams[j], stringTable, getStyles, 1);
                }
                else
                {
                    j--;
                }
            }
            returnedlist = (List<List<string>>)returnlists[0];
            headers = (List<List<string>>)returnlists[1];

            if (returnedlist.Count() == 0)
            {
                MessageBox.Show("Empty Workbook please select another");
                return null;
            }
            else
            {

                return GenerateData(returnedlist, headers);
                
            }


        }

    }
}
